-- 建表

CREATE TABLE hotel_text (
id int,Name string,
CtfTp string,
CtfId int,
Gender string,
Birthday string,
Address string ,
Zip string ,
Duty string,
Mobile string,
Tel string,
Fax string,
EMail string,
Nation string,
Taste string,
Education string,
Company string,
Family string,
Version string,
Hotel string,
Grade string,
Duration string,
City string) ROW FORMAT delimited
fields terminated by ','
stored as textfile;


CREATE TABLE hotel_parquet (
id int,Name string,
CtfTp string,
CtfId int,
Gender string,
Birthday string,
Address string ,
Zip string ,
Duty string,
Mobile string,
Tel string,
Fax string,
EMail string,
Nation string,
Taste string,
Education string,
Company string,
Family string,
Version string,
Hotel string,
Grade string,
Duration string,
City string,
leave_time string) ROW FORMAT delimited
fields terminated by ','
stored as parquet;

INSERT OVERWRITE TABLE hotel_parquet FROM 
SELECT *,
date_add(Version,int(Duration/24) + (hour(Version + Duration - int(Duration/24)))/24) 
+ Duration - int(Duration/24) leave_time  FROM hotel_text 



--根据总时长和入住时间计算用户离开时间，并插入数据中

-- 统计各个城市的酒店数量，按照城市入住人数降序
SELECT City,COUNT(Hotel) hotel_count FROM hotel_parquet  GROUP BY City ORDER BY hotel_count DESC;


-- 统计每年每月每个酒店的使用天数，一个酒店一月内有 30 个房间被入住 1 天,7 个房间被入住两天，
-- 则使用天数为 1*30+2*7，44个天数（客户入住总时长/24时，不足一天的也按一天算）

SELECT Hotel,substring(Version,1,7),SUM(int((Duration+23)/24)) count_of_day

 FROM hotel_parquet GROUP BY Hotel,substring(Version,1,7);

-- 统计各个城市各个酒店回头客所占百分比（同一顾客两次及以上入住同一酒店/该酒店入住过的顾客数量） 
-- 同一顾客两次及以上入住同一酒店
-- 该酒店入住过的顾客数量
SELECT 
t0.City,
t0.Hotel,
SUM(if(t0.order_count>=2,1,0))/COUNT(id)
FROM(
    SELECT
    City, 
    Hotel,
    id,
    COUNT(id) order_count,
    FROM hotel_parquet GROUP City,Hotel,id
)t0 GROUP City,Hotel


-- 统计每年每个城市的酒店的黄金月份（入住时间在这个月，
-- 离开时间在另一个月，则该期间的月份入住次数都要加一）（黄金月份：入住此数最多的那个月） 
SELECT City,Hotel,count1+count2
FROM 
(
    SELECT 
        City,
        Hotel,
        COUNT(id) count1 FROM hotel_parquet GROUP BY City,Hotel,substring(Version,1,7); 
        --只计算了本月入住的
) t0 JOIN
(
    SELECT 
            City,
            Hotel,
            COUNT(id) count2 FROM hotel_parquet GROUP BY City,Hotel,substring(leave_time,1,7) 
            WHERE Version = add_months(Version,-1);  
            -- 入住时间是上个月 离开时间是本月
) t1 ON t0.City = t1.City & t0.Hotel = t1.Hotel;

-- 每年连锁酒店各个城市的销量情况，
SELECT 
City,
Hotel,
COUNT(id) sale
FROM hotel_parquet GROUP BY City,Hotel HAVING COUNT(hotel) >= 2;

-- 以及该酒店在销量最大的城市与最小的城市之间的差值（连锁酒店：在多个城市都有该酒店）
SELECT 
Hotel,
MAX(sale) - MIN(sale) sale_diff FROM
(
    SELECT 
    City,
    Hotel,
    COUNT(id) sale
    FROM hotel_parquet GROUP BY City,Hotel HAVING COUNT(hotel) >= 2
) GROUP BY Hotel ;



-- 根据顾客的出生日期，判定每年酒店入住的主力军是哪个年代的人
-- （哪个年代：是70、80、还是90后，主力军：入住总时长/入住次数 最大的那个）

SELECT t0.*,duration_of_count FROM
(
SELECT 
YEAR(Version) annually,
regexp_replace(substring(Birthday,1,3),'\\d{2}(\\d)','$10') years, 
SUM(Duration)/COUNT(id) OVER(PARTITION BY YEAR(Version) ORDER BY SUM(Duration)/COUNT(id) ) duration_of_count
FROM hotel_parquet
 GROUP BY 
 YEAR(Version)
) t0 WHERE t0.duration_of_count = 1;

